﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook
{
    public class ChartOfAccountManager:ConnectionManager
    {
        public SqlException AddChartOfAccount(ChartOfAccountUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "INSERT INTO OACT (AcctType, IsAccountTitle, FatherAcctCode, AcctCode, AcctName, AcctStatus, DateCreated, CreatedBy, DateModified, ModifiedBy) VALUES (@AcctType, @IsAccountTitle, @FatherAcctCode, @AcctCode, @AcctName, @AcctStatus, @DateCreated, @CreatedBy, @DateModified, @ModifiedBy)";

                oCommand.Parameters.Add(new SqlParameter("@AcctType", oUnit.AcctType));
                oCommand.Parameters.Add(new SqlParameter("@IsAccountTitle", oUnit.IsAccountTitle));
                oCommand.Parameters.Add(new SqlParameter("@FatherAcctCode", oUnit.FatherAcctCode));
                oCommand.Parameters.Add(new SqlParameter("@AcctCode", oUnit.AcctCode));
                oCommand.Parameters.Add(new SqlParameter("@AcctName", oUnit.AcctName));
                oCommand.Parameters.Add(new SqlParameter("@AcctStatus", oUnit.AcctStatus));
                oCommand.Parameters.Add(new SqlParameter("@DateCreated", oUnit.DateCreated));
                oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally { oCommand.Dispose(); }
        }

        public SqlException UpdateChartOfAccount(ChartOfAccountUnit oUnit)
        {

            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE OACT SET AcctName=@AcctName, IsAccountTitle=@IsAccountTitle, FatherAcctCode=@FatherAcctCode AcctStatus=@AcctStatus,  ModifiedBy=@AcctModifiedBy WHERE AcctCode=@AcctCode";
                oCommand.Parameters.Add(new SqlParameter("@IsAccountTitle", oUnit.IsAccountTitle));
                oCommand.Parameters.Add(new SqlParameter("@FatherAcctCode", oUnit.FatherAcctCode));
                oCommand.Parameters.Add(new SqlParameter("@AcctCode", oUnit.AcctCode));
                oCommand.Parameters.Add(new SqlParameter("@AcctName", oUnit.AcctName));
                oCommand.Parameters.Add(new SqlParameter("@AcctStatus", oUnit.AcctStatus));

                oCommand.Parameters.Add(new SqlParameter("@AcctModifiedBy", oUnit.ModifiedBy));


                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public DataTable GetChartOfAccounts()
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataTable dt = new DataTable();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT *, AcctCode AcctCode2, FatherAcctCode FatherAcctCode2 FROM OACT ORDER BY AcctCode";
            oAdapter.SelectCommand = oCommand;
            
            oAdapter.Fill(dt);
            return dt;
        }

        public DataTable GetChartOfAccountByAcctCode(string sAcctCode)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataTable dt = new DataTable();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OACT WHERE AcctCode=@AcctCode";
            oCommand.Parameters.Add(new SqlParameter("AcctCode", sAcctCode));
            oAdapter.SelectCommand = oCommand;

            oAdapter.Fill(dt);
            return dt;
        }
        public DataTable BrowseChartOfAccountByAcctCode(string sAcctCode)
        {
            sAcctCode = "%" + sAcctCode + "%";

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataTable dt = new DataTable();

            oCommand.Connection = this.Connection;

            oCommand.CommandText = "SELECT * FROM OACT WHERE AcctCode LIKE @AcctCode AND IsAccountTitle='N'";
            oCommand.Parameters.Add(new SqlParameter("@AcctCode", sAcctCode));
            oAdapter.SelectCommand = oCommand;

            oAdapter.Fill(dt);
            return dt;
        }

        public DataTable BrowseChartOfAccountByAcctName(string sAcctName)
        {

            sAcctName = "%" + sAcctName + "%";
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataTable dt = new DataTable();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OACT WHERE AcctName Like @AcctName AND IsAccountTitle='N'";
            oCommand.Parameters.Add(new SqlParameter("@AcctName", sAcctName));
            oAdapter.SelectCommand = oCommand;

            oAdapter.Fill(dt);
            return dt;
        }

        public DataTable GetChartOfAccountByAccountType(bool IsAccountTitle)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataTable dt = new DataTable();

            oCommand.Connection = this.Connection;
            if (IsAccountTitle)
            {
                oCommand.CommandText = "SELECT AcctCode, AcctName FROM OACT WHERE IsAccountTitle='Y'";
            }
            else
            {
                oCommand.CommandText = "SELECT AcctCode, AcctName FROM OACT WHERE IsAccountTitle='N'";
            }
            
            oAdapter.SelectCommand = oCommand;

            oAdapter.Fill(dt);
            return dt;
        }

    }
}
